82

Beginner’s Guide to Code Algorithms

82

Step 5: Get all file and folder names and store in the new worksheet(s) created

in step 1

Step 6: Save in a new workbook

Note that steps 3 and 5 are similar, because you have to read the headers of all file

objects to determine how many there are. It is necessary to do this so that a message

with progress status can be displayed.

Note that the code in steps 3 and 5 calls itself. This is known as “recursion”.

Here is the code:

STEP 1

Public i As Double, k As Double, wb As Workbook, ws As Worksheet, j As Double,

NoData As Integer, B As String

Public OverallTime As Double

Public fs As New FileSystemObject

Public FSO As New FileSystemObject

Sub Macro1()

Dim A As String, Folder As String, FileName As String

 

  B =​ Now

  OverallTime =​ 0

  ‘A =​ DateDiff(“s”, Now, B)

 

 

  Set wb =​ Workbooks.Add

    With wb

      .Worksheets(1).Name =​ “Folders”

    End With

 

  Set ws =​ wb.Sheets.Add

    With ws

      .Name =​ “Errors”

    End With

 

  wb.Sheets(“Folders”).Activate

 

  NoData =​ 0

  i =​ 2

  k =​ 1

  Cells(1, 1) =​ “Name”

  Cells(1, 2) =​ “Type”

  Cells(1, 3) =​ “Size”

  Cells(1, 4) =​ “Path”

  Cells(1, 5) =​ “Last Modified on”

  Call PickFolder(Folder)

  j =​ 0

  Set FSO =​ CreateObject(“Scripting.FileSystemObject”)

  Call GetFolderNames2(Folder)